package uk.ac.cam.bt288.Pico2;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter {
	public static final String KEY_ROWID = "id";
	public static final String KEY_APPKEY = "app_key";
	public static final String KEY_USERNAME = "username";
	public static final String KEY_PUBKEY = "public_key";
	public static final String KEY_PRIVKEY = "private_key";
	public static final String APP_KEY = "app_key";
	public static final String APP_NAME = "app_name";
	
	private static final String TAG = "DBAdapter";
	
	private static final String DATABASE_NAME = "pico";
	private static final String TBL_STORAGE = "storage";
	private static final String TBL_WHITELIST = "whitelist";
	private static final int DATABASE_VERSION = 3;
	
	private static final String DATABASE_CREATE = 
		"create table storage (id integer primary key autoincrement, " +
		"app_key text not null, username text not null, public_key text not null, private_key text not null);";
				
	private static final String CREATE_WHITELIST =	
		"create table whitelist (id integer primary key autoincrement, " +
		"app_key text not null, app_name text not null);";
	
	private final Context context;
	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;
	
	//extend SQLiteOpenHelper to provide more functionalities
	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		
		@Override
		public void onCreate (SQLiteDatabase db) {
			try {
				db.execSQL(DATABASE_CREATE);
				db.execSQL(CREATE_WHITELIST);
				String appPubKey = "MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCikfx50Os0fyvImBLjSqLV5mA0VZl0s1RMZQsJA32/0G9bUhPXYppy0q/P+LgTAXWuA3rIAk/6AfZ1AfIyfoXG9zecSCv7Np4gZ40rQG+8jI9HznqJGw4gviP+Gs3R1VMk8PuZKHcD18ZAe+mQuWX+vemfytZudQyFrSjH5QYx0wIDAQAB";
				ContentValues val = new ContentValues();
				val.put(APP_KEY, appPubKey);
				val.put(APP_NAME, "Demo Application");
				db.insert(TBL_WHITELIST, null, val);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		@Override
		public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version "+oldVersion+" to "+newVersion+", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS storage");
			onCreate(db);
		}
		
	}
	
	public DBAdapter (Context ctx) {
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}
	
	//load database
	public DBAdapter load() throws SQLException {
		db = DBHelper.getWritableDatabase();
		return this;
	}
	
	//close database
	public void close(){
		DBHelper.close();
	}
	
	//insert new entry into database
	public long insertEntry(String app_key, String username, String pubKey, String privKey){
		ContentValues val = new ContentValues();
		val.put(KEY_APPKEY, app_key);
		val.put(KEY_USERNAME, username);
		val.put(KEY_PUBKEY, pubKey);
		val.put(KEY_PRIVKEY, privKey);
		return db.insert(TBL_STORAGE, null, val);
	}
	
	//delete entry from database
	public boolean deleteEntry (long rowId){
		return db.delete(TBL_STORAGE, KEY_ROWID + "=" + rowId, null) > 0;
	}
	
	//delete all
	public boolean deleteAll () {
		return db.delete(TBL_STORAGE, null, null) > 0;
	}
	
	//retrieve application name
	public String getAppName(String pubKey) {
		int APPNAME = 2;
		Cursor c = db.query(true, TBL_WHITELIST, new String[]{KEY_ROWID, APP_KEY, APP_NAME}, APP_KEY + "=\"" + pubKey+"\"", null, null, null, null, null);
		if (c.getCount() != 1) {
			return null;
		}
		c.moveToFirst();
		return c.getString(APPNAME);
	}
	
	//retrieve entries given app name
	public Cursor appAccounts (String pubKey) {
		return db.query(TBL_STORAGE, new String[]{KEY_ROWID, KEY_APPKEY, KEY_USERNAME, KEY_PUBKEY, KEY_PRIVKEY}, KEY_APPKEY + "=\"" + pubKey+"\"", null, null, null, null);
	}
	
	//retrieve all entries
	public Cursor getAll(){
		return db.query(TBL_STORAGE, new String[] {KEY_ROWID, KEY_APPKEY, KEY_USERNAME, KEY_PUBKEY, KEY_PRIVKEY}, null, null, null, null, null);
	}
	
	//retrieve a particular entry
	public Cursor getEntry (long rowId) throws SQLException {
		Cursor rtn = db.query(true, TBL_STORAGE, new String[] {KEY_ROWID, KEY_APPKEY, KEY_USERNAME, KEY_PUBKEY, KEY_PRIVKEY}, KEY_ROWID + "=" + rowId, null, null, null, null, null);
		if (rtn != null)
			rtn.moveToFirst();
		return rtn;
	}
	
	//update an entry
	public boolean updateEntry(long rowId, String app_key, String username, String pubKey, String privKey) {
		ContentValues val = new ContentValues();
		val.put(KEY_APPKEY, app_key);
		val.put(KEY_USERNAME, username);
		val.put(KEY_PUBKEY, pubKey);
		val.put(KEY_PRIVKEY, privKey);
		return db.update(TBL_STORAGE, val, KEY_ROWID + "=" + rowId, null) > 0;
	}
}
